package com.erp.dao.impl;

import com.erp.bean.Commodity;
import com.erp.dao.CommodityDao;
import com.erp.util.db.DataBaseDao;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zch
 * 商品数据访问实现类
 */
public class CommodityDaoImpl extends DataBaseDao implements CommodityDao {
    @Override
    public List<Commodity> findAll() {
        List<Commodity> list = new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "select `gid`,`gname`,`price`,`model`,`prddate`,`supid`,tyid from commodity";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                Commodity commodity = new Commodity(rs.getInt(1),rs.getString(2)
                        ,rs.getDouble(3),rs.getString(4),rs.getDate(5),
                        rs.getInt(6),rs.getInt(7));
                list.add(commodity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public int add(Commodity commodity) {
        int i = 0;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "insert into commodity (gname,price,model,prddate,supid,tyid) values(?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,commodity.getGname());
            pstmt.setDouble(2,commodity.getPrice());
            pstmt.setString(3,commodity.getModel());
            pstmt.setDate(4,new Date(commodity.getPrddate().getTime()));
            pstmt.setInt(5,commodity.getSupid());
            pstmt.setInt(6,commodity.getTyid());
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
        }
        return i;
    }

    @Override
    public Commodity findById(int gid) {
        Commodity commodity = null;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "select `gid`,`gname`,`price`,`model`,`prddate`,`supid`,tyid from commodity where gid = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,gid);
            rs = pstmt.executeQuery();
            while (rs.next()){
                commodity =new Commodity(rs.getInt(1),rs.getString(2)
                        ,rs.getDouble(3),rs.getString(4),rs.getDate(5),
                        rs.getInt(6),rs.getInt(7));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return commodity;
    }

    @Override
    public int update(Commodity commodity) {
        int i = 0;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "update commodity set gname=?,price=?,model=?,prddate=?,supid=?,tyid=? where gid=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,commodity.getGname());
            pstmt.setDouble(2,commodity.getPrice());
            pstmt.setString(3,commodity.getModel());
            pstmt.setDate(4,new Date(commodity.getPrddate().getTime()));
            pstmt.setInt(5,commodity.getSupid());
            pstmt.setInt(6,commodity.getTyid());
            pstmt.setInt(7,commodity.getGid());
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return i;
    }

    @Override
    public Commodity findByName(String gname) {
        Commodity commodity = null;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "select `gid`,`gname`,`price`,`model`,`prddate`,`supid`,tyid from commodity where gname = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,gname);
            rs = pstmt.executeQuery();
            while (rs.next()){
                commodity =new Commodity(rs.getInt(1),rs.getString(2)
                        ,rs.getDouble(3),rs.getString(4),rs.getDate(5),
                        rs.getInt(6),rs.getInt(7));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return commodity;
    }

    @Override
    public List<Commodity> find(String columm, String values) {
        List<Commodity> list = new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "select * from commodity where "+columm+" like ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"%"+values+"%");
            rs = pstmt.executeQuery();
            while (rs.next()){
                Commodity commodity =new Commodity(rs.getInt(1),rs.getString(2)
                        ,rs.getDouble(3),rs.getString(4),rs.getDate(5),
                        rs.getInt(6),rs.getInt(7));
                list.add(commodity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public List<Commodity> findByTyid(int tyid) {
        List<Commodity> list = new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "select * from commodity where tyid = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,tyid);
            rs = pstmt.executeQuery();
            while (rs.next()){
                Commodity commodity =new Commodity(rs.getInt(1),rs.getString(2)
                        ,rs.getDouble(3),rs.getString(4),rs.getDate(5),
                        rs.getInt(6),rs.getInt(7));
                list.add(commodity);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }

        return list;
    }
}
